# -*- coding: utf-8 -*-
# Copyright (c) 2021, Fisher and contributors
# For license information, please see license.txt

from __future__ import unicode_literals
import copy
from datetime import date, datetime, timedelta
import frappe
from frappe import _
from frappe.model.naming import getseries
from frappe.model.document import Document
from zelin_am.utils import *
from frappe.utils import ceil, flt, cstr, nowdate, getdate, nowtime,rounded, get_user_date_format, add_days, today, now
from pprint import pprint

reschedule_item_fields = ["ref_doctype", "ref_docname", "ref_doc_item","ref_doc_item_idx",
            "item_code","item_name","warehouse", "qty", "original_qty", "stock_uom", "due_date",
             "rescheduled_date","rescheduled_qty","supplier"]

class MultiLevelMRP(Document):    
    def initialize(self):        
        self.plan_order_id = 0
        self.mrp_inventory_id = 0
        self.warehouses = frappe.get_all('Warehouse', filters={'company': self.company, 'mrp_exclude':0}, pluck='name')
        self.holidays = self.get_holidays()
        self.plan_days_map = frappe._dict()
        self.item_plan_master_map = get_item_plan_master_map(self.company)
        self.whole_number_uom_list = set(frappe.get_all("UOM", filters={"must_be_whole_number":1}, pluck = "name"))
        self.bom_map = get_bom_map(self.company)
        self.item_code_onhand_map = get_item_code_onhand_map(self.company)
        self.plan_orders = []
        self.plan_order_items = []
        self.rescheduled_items_map = {}  #{(ref_docname, ref_doc_item, reschedule_date):{reschedule record}} 
        self.logs = []
        self.debug_items_set = set(self.debug_items.split("\n")) if self.debug_items else set()

    def get_holidays(self):
        holiday_list = frappe.get_value('Company',  self.company,  "default_holiday_list")
        if holiday_list:
            holidays = frappe.db.sql_list('''select holiday_date from `tabHoliday` where parent=%s''', holiday_list)
            if holidays:
                return set(holidays)

    def get_item_code_onhand(self, item_code):
        qty_dict = self.item_code_onhand_map.get(item_code)
        return qty_dict and qty_dict.onhand or 0

    def log(self, step, message):
        if frappe.flags.multi_level_mrp_debug: print(step, message)
        if not hasattr(self, 'logs'):
            self.logs = []
        self.logs.append([self.name,"Multi Level MRP", "mrp_run_log",_(step), nowtime(), _(message)])

    def _mrp_cleanup(self):
        self.log('_mrp_cleanup', "Start MRP Cleanup")
        filter = {'mrp': self.name}        
        frappe.db.delete("MRP Plan Item",{'parent': self.name})
        frappe.db.delete("MRP Run Log",{'parent': self.name})
        self.mrp_run_log = []
        sql = """delete pi from `tabPlan Order Item` pi 
                    inner join `tabPlan Order` p on pi.parent=p.name
                    where is_fixed = 0"""
        sql1 = """delete p from `tabPlan Order` p 
                    where is_fixed = 0"""
        sql2 = """delete p from `tabMRP Inventory` p """
        sql3 = """delete resc from `tabRescheduled Item` resc """
        for s in [sql, sql1, sql2, sql3]:
            frappe.db.sql(s)
        self.log('_mrp_cleanup', "End MRP Cleanup")
        return True

    def _low_level_code_calculation(self):
        self.log("_low_level_code_calculation","Start low level code calculation")
        counter = 999999
        llc = 0
        filter = {'company': self.company}
        frappe.db.set_value('Item Default', filter,{"llc": llc})
        filter.update({"llc":llc})        
        products = frappe.db.get_values('Item Default', filter)
        if products: counter = len(products)
        log_msg = _("Low level code 0 finished - Nbr. products: {0}").format(counter)        
        self.log("_low_level_code_calculation",log_msg)

        sql = """select BI.item_code from `tabBOM` B inner join `tabBOM Item` BI on B.name = BI.parent 
                    inner join `tabItem Default` header_item on B.item = header_item.parent and
                                header_item.company = B.company
                    inner join `tabItem Default` child_item on BI.item_code = child_item.parent and
                                child_item.company = B.company                                
                    where B.docstatus=1 and B.is_default=1 and 
                        child_item.llc = %s and header_item.name in %s"""
        # and B.is_default=1 把这个默认标志移出来，因为可能非默认的物料清单也会被使用    
        while counter:
            llc += 1
            if frappe.flags.multi_level_mrp_debug: print('llc=%s' % llc)
            filter.update({"llc":llc - 1})
            products = frappe.db.get_values('Item Default', filter)            
            if products:        
                bom_lines = frappe.db.sql(sql, (llc - 1, [p[0] for p in products]))
                if bom_lines:
                    frappe.db.set_value('Item Default',
                        {'company': self.company, 'parent':('in',[b[0] for b in bom_lines])},
                        {"llc": llc})
            filter.update({"llc":llc})            
            counter = frappe.db.count('Item Default', filter)
            log_msg = _("Low level code {0} finished - Nbr. products: {1}").format(llc, counter)            
            self.log("_low_level_code_calculation",log_msg)
            if llc > 20: break          #avoid endless loop due to recursive bom            

        mrp_lowest_llc = llc
        self.log("_low_level_code_calculation","End low level code calculation")        
        return mrp_lowest_llc

    @frappe.whitelist()
    def get_plan_items(self, save_db = False):
        self.items = []
        self.plan_items = []
        if not hasattr(self, 'warehouses'):
            self.initialize()
            self._mrp_cleanup()
        self.log('get plan items', 'Start get plan items')
        #先根据筛选条件获取销售订单及其相关物料号
        so_list, mr_list = [],[]   # include both sales order and material request (forecast)
        filters = frappe._dict({})
        for f in ['sales_order','item_code','from_delivery_date', 'to_delivery_date',
                'from_date', 'to_date','customer','project','mrp_warehouse']:
            filter_value = self.get(f)
            if filter_value:
                filters[f] = filter_value
        if filters.get('mrp_warehouse'):    #avoid auto default warehouse by system
            filters.warehouse = filters.pop('mrp_warehouse')
        get_items_from = self.get_items_from
        if not get_items_from or get_items_from == 'Sales Order':
            so_list = get_open_so(self.warehouses, filters = filters, sort_key = 21)
            if so_list and filters.get('sales_order'):
                copied_filters = copy.deepcopy(filters)
                item_code_list = list({so.item_code for so in so_list})
                copied_filters.pop('sales_order')
                #重新基于筛选出的物料号筛选其它销售订单
                so_list = get_open_so(self.warehouses, item_code_list = item_code_list, filters = copied_filters, sort_key = 21)
        if not get_items_from or get_items_from == 'Material Request':
            mr_list = get_open_mr_as_forecast(self.warehouses, filters = filters, sort_key = 20)
        if not mr_list and not so_list: return

        item_code_list = []
        if filters:
            processed_bom_set, sub_item_set = set(), set()
            bom_no_list = {row.bom_no for row in so_list + mr_list if row.bom_no}
            fg_item_code_set = {row.item_code for row in so_list + mr_list}
            get_bom_list_sub_items(bom_no_list, self.bom_map, processed_bom_set, sub_item_set)
            item_code_list = list(fg_item_code_set.union(sub_item_set))
        wo_list =  get_open_wo(self.warehouses, item_code_list = item_code_list, sort_key = 11)
        po_list =  get_open_po(self.warehouses, item_code_list = item_code_list, sort_key = 12)
        for data in (so_list, mr_list, wo_list, po_list):    #self.remove_planned_orders
            if data: self.plan_items.extend(data)

        self.process_already_planned_qty()

        for (f, sort_key) in (
                            (get_open_wo_gi,22),
                            (get_open_mr,13),
                            (get_open_mr_gi,23),
                            (get_open_subcontract_gi,24)
        ):
            data = f(self.warehouses, item_code_list = item_code_list, sort_key = sort_key)
            if data: self.plan_items.extend(data)

        self.plan_items.extend(get_open_plan_order(self.company, self.warehouses, mrp = self.name, item_code_list = item_code_list, sort_key = 14))
        self.plan_items.extend(get_open_plan_order_items(self.company, self.warehouses,  mrp = self.name, item_code_list = item_code_list, sort_key = 25))
        self.plan_items = sorted(self.plan_items, key=lambda k: (k['item_code'], k['due_date'], k['sort_key']))

        self.extend('items', self.plan_items)    
        self.log('get plan items', 'End get plan items')

    def process_already_planned_qty(self):
        """map sales order item with planned work order and purchase order"""
        self.log("process_already_planned_qty",_("Start") + _("Process Already Planned Qty"))
        so_list = [item for item in self.plan_items if item.ref_doctype == 'Sales Order']
        for so in so_list:
            wo_po_list = [item for item in self.plan_items if item.sales_order == so.ref_docname 
                and item.sales_order_item == so.ref_doc_item]
            so.order_open_qty = so.qty
            order_open_qty = so.qty * -1
            for wo_po in wo_po_list:
                wo_po.order_open_qty = wo_po.qty
                allotted_qty = wo_po.qty if (wo_po.qty <= order_open_qty) else order_open_qty
                order_open_qty -= allotted_qty
                wo_po.planned_qty = allotted_qty
                wo_po.qty -= allotted_qty
                so.qty += allotted_qty
                so.planned_qty = (so.planned_qty or 0) + allotted_qty
                if order_open_qty <=0: break
        self.log("process_already_planned_qty",_("End") + _("Process Already Planned Qty"))

    def _prepare_plan_item_data_bom_explosion(self, item_plan_master_doc, bomline,
        qty, mrp_date_demand_2, name, plan_order = None, customer=None):
        key = item_plan_master_doc.item_code
        bom_component = self.item_plan_master_map.get(bomline.item_code, {})
        bom_no = (self.sub_assembly_bom_map.get(bomline.item_code) or 
                    bomline.bom_no or bom_component.get('default_bom'))
        # 单用量乘以订单需求量            
        qty = self.adjust_qty(bomline.stock_qty  / bomline.quantity * qty, bomline) * -1         
        return frappe._dict({
            "parent" : plan_order,
            "parenttype": "Plan Order",
            "parentfield": "items",
            "item_code": bomline.item_code,
            "item_name": bomline.item_name,
            "bom_no": bom_no,
            "warehouse": bom_component.get('default_warehouse'),
            "qty": qty,  
            "stock_uom":bomline.stock_uom,
            "ref_doctype": "Plan Order",
            "ref_docname": plan_order,
            "due_date": mrp_date_demand_2,            
            "parent_item_code": bomline.item,       #来自bom map中bom header的item 字段
            "note": _("Demand Bom Explosion: {0}").format(name or item_plan_master_doc.item_code),
            "sort_key": 25,
            "customer":customer
        })    
    
    def explode_action(self, item_plan_master_doc, mrp_action_date, name, qty, plan_order = None, 
                    customer = None, bom_no=None):
        """Explode requirements."""
        mrp_date_demand = mrp_action_date
        for bomline in self.bom_map.get(bom_no, []):
            # if no item master company default record, no plan order will be created because no low level code
            if bomline.stock_qty <= 0.00:   # or bomline.item_code.type != "product":
                continue
            plan_item = self._prepare_plan_item_data_bom_explosion(item_plan_master_doc, 
                bomline, qty, mrp_date_demand, name, plan_order = plan_order, customer = customer)
            self.plan_order_items.append(plan_item)
        return True    
    
    def _exclude_from_mrp(self, item_code, mrp_explosion = 0):
        """ To extend with various logic where needed. """                                 
        return False      
    
    def _init_plan_item_grouped_demand(self, nbr_create, item_plan_master_doc):
        last_date = None
        last_qty = 0.00
        item_code = item_plan_master_doc.item_code
        safety_stock = item_plan_master_doc.safety_stock or 0
        onhand = self.get_item_code_onhand(item_code)
        grouping_delta = item_plan_master_doc.consolidate_demand_days or 0
        move_list = [p for p in self.plan_items if p.item_code == item_code]
        for move in move_list: 
            mrp_date, mrp_qty = (move.due_date, move.qty)           
            if self._exclude_move(move):
                continue
            if (
                last_date
                and (mrp_date >= last_date + timedelta(days=grouping_delta))
                and ( (onhand + last_qty + mrp_qty) < safety_stock
                    or (onhand + last_qty) < safety_stock
                )
            ):
                note = _("Grouped Demand for {0} Days").format(grouping_delta)
                qtytoorder = safety_stock - onhand - last_qty
                cm = self.create_action(
                    item_plan_master_doc=item_plan_master_doc,
                    mrp_date=last_date,
                    mrp_qty=qtytoorder,
                    ref_docname = move.ref_docname,
                    ref_doc_item = move.ref_doc_item,
                    ref_doc_item_idx = move.ref_doc_item_idx,
                    #ref_docname=note,
                    note = note,
                    warehouse = move.warehouse,
                    customer = move.customer,
                    bom_no = move.bom_no
                )
                qty_ordered = cm.get("qty_ordered", 0.0)
                onhand = onhand + last_qty + qty_ordered
                last_date = None
                last_qty = 0.00
                nbr_create += 1
            if (
                (onhand + last_qty + mrp_qty) < safety_stock
                or (onhand + last_qty) < safety_stock
            ):
                if not last_date or last_qty == 0.0:
                    last_date = mrp_date
                    last_qty = mrp_qty
                else:
                    last_qty += mrp_qty
            else:
                last_date = mrp_date
                onhand += mrp_qty

        if last_date and last_qty != 0.00:
            note = _("Grouped Demand for {0} Days").format(grouping_delta)
            qtytoorder = safety_stock - onhand - last_qty
            cm = self.create_action(item_plan_master_doc=item_plan_master_doc, 
                mrp_date=last_date,
                mrp_qty=qtytoorder,
                note=note,
                customer = move.customer,
                bom_no = move.bom_no)
            qty_ordered = cm.get("qty_ordered", 0.0)
            onhand += qty_ordered
            nbr_create += 1
        return nbr_create
    
    def _exclude_move(self, move):
        """Improve extensibility being able to exclude special moves."""
        return False

    def _mrp_calculation(self, mrp_lowest_llc):
        def get_move_list_log(move_list):
            return '\n'.join([get_move_log(move) for move in move_list])
        
        def get_move_log(move):
            fields = ['ref_doctype','ref_docname','qty','due_date','parent_item_code']
            return ';'.join([str(move.get(f) or '') for f in fields])

        self.log("_mrp_calculation", "Start MRP calculation")
        self.sub_assembly_bom_map = {row.item_code:row.mrp_bom_no for row in self.sub_assembly_bom}
        year_last_2 = str(getdate().year)[-2:]
        self.year_last_2 = year_last_2
        self.mrp_id = getseries(f'PL{year_last_2}', 4)                    
        llc = 0
        #self.plan_items包括销售订单与在途， self.plan_order_items包括计划订单中的相关需求
        #plan_items = self.plan_items + self.plan_order_items
        #plan_items = sorted(plan_items, key=lambda k: (k['item_code'], k['due_date'], k['sort_key']))
        while mrp_lowest_llc > llc:
            item_plan_master_list =[item for item in self.item_plan_master_map.values() if item.llc == llc] 
            llc += 1
            counter = 0
            for item_plan_master_doc in item_plan_master_list:
                item_code, consolidate_demand_days,supply_method = (
                    item_plan_master_doc.item_code, item_plan_master_doc.consolidate_demand_days,
                    item_plan_master_doc.supply_method)
                safety_stock = item_plan_master_doc.safety_stock or 0
                nbr_create = 0
                onhand = 0
                if not item_plan_master_doc.ignore_existing_stock:
                    onhand = self.get_item_code_onhand(item_code)
                if not consolidate_demand_days or consolidate_demand_days == 0:
                    #todo 需要按日期，供应、需求排序
                    move_list = [r for r in self.plan_items + self.plan_order_items if r.item_code == item_code]
                    move_list = sorted(move_list, key=lambda k: (k['due_date'], k['sort_key'], k['ref_docname']))
                    if item_code in self.debug_items_set:
                        self.log(f"{item_code} move_list",get_move_list_log(copy.deepcopy(move_list)))
                    #add safety stock as separate first move row, avoid plan order qty over sales order qty
                    safety_stock_row = []
                    if safety_stock:
                        safety_stock_row = [frappe._dict({
                            'qty' : -safety_stock,
                            'due_date': self.plan_days(getdate(), item_plan_master_doc.lead_time_days or 0),
                            'note': _("Safety Stock")
                        })]
                    for (idx, move) in enumerate(safety_stock_row + move_list):
                        #供后面的MRP inventory用，避免重排修改原数量后对MRP库存滚动计数的影响
                        if not move.original_qty: move.original_qty = move.qty
                        #if frappe.flags.multi_level_mrp_debug: print('move =', move)                            
                        if self._exclude_move(move):
                            continue
                        #受托加工物料不考虑现有库存    
                        if supply_method == "Customer Provided" and self.customer_provided_as_is:
                            qtytoorder = move.qty * -1
                        else:  
                            qtytoorder = - onhand - move.qty
                        if item_code in self.debug_items_set:
                            self.log(f"{idx} {item_code} onhand: {onhand} toorder: {qtytoorder}",get_move_log(copy.deepcopy(move)))
                        due_date = move.due_date
                        #考虑销售出货拣料包装时间，即工单或采购订单提前多少天入库
                        if move.ref_doctype == 'Sales Order' and item_plan_master_doc.pick_pack_days:
                            move.due_date = self.plan_days(move.due_date, item_plan_master_doc.pick_pack_days * -1)
                        if frappe.flags.multi_level_mrp_debug: print('qtytoorder=', qtytoorder, onhand, move.qty)                                                    
                        if qtytoorder > 0.0:
                            qtytoorder = self.adjust_qty(qtytoorder, item_plan_master_doc)
                            #重排(将交期提前)未来订单以满足当前需求，不用生成计划订单，将重排订单添加到订单重排建议表中。
                            next_moves = [move for (i, move) in enumerate(move_list) if move.qty > 0 and i > idx]
                            if item_code in self.debug_items_set:
                                self.log(f"{item_code} next_moves",get_move_list_log(copy.deepcopy(next_moves)))
                            qty_rescheduled = 0
                            if not (supply_method == "Customer Provided" and self.customer_provided_as_is):
                                for (ii, next_move) in enumerate(next_moves):
                                    if not next_move.original_qty: next_move.original_qty = next_move.qty
                                    qty_tobe_rescheduled = qtytoorder -qty_rescheduled
                                    if next_move.qty >= qty_tobe_rescheduled:
                                        assigned_qty = qty_tobe_rescheduled
                                    else:
                                        assigned_qty = next_move.qty
                                    next_move.qty -= assigned_qty       #这里需扣减掉，免后面的其它需求继续耗用，或本行虚增可用库存    
                                    qty_rescheduled += assigned_qty
                                    
                                    self.add_rescheduled_record(next_move, assigned_qty, move.due_date, move.ref_docname)
                                    if qty_rescheduled >= qtytoorder: break
                            if item_code in self.debug_items_set:
                                self.log(f"{idx} {item_code} qty_rescheduled: {qty_rescheduled}",'')
                            if (qtytoorder - qty_rescheduled) > 0:
                                #增加销售订单出货处理（拣料与包装时间(天)，默认一天
                                cm = self.create_action(item_plan_master_doc=item_plan_master_doc, mrp_date = move.due_date,
                                    mrp_qty = qtytoorder - qty_rescheduled,
                                    ref_doctype = move.ref_doctype,
                                    ref_docname = move.ref_docname,
                                    ref_doc_item = move.ref_doc_item,
                                    ref_doc_item_idx = move.ref_doc_item_idx,
                                    note = move.note,
                                    warehouse = move.warehouse,
                                    customer = move.customer,
                                    bom_no = move.bom_no
                                )
                                qty_ordered = cm["qty_ordered"]
                                nbr_create += 1 
                            else:
                                qty_ordered = 0
                            onhand += move.qty + qty_ordered + qty_rescheduled
                        else:
                            onhand += move.qty
                        #这里要记录一个需求，由哪些供应 库存，在途，计划满足了    
                else:
                    if item_code in self.debug_items_set:
                        self.log(f"{item_code} consolidate_demand_days: {consolidate_demand_days}",'')
                    nbr_create = self._init_plan_item_grouped_demand(nbr_create, item_plan_master_doc)

                if onhand < safety_stock and nbr_create == 0:
                    qtytoorder = item_plan_master_doc.safety_stock - onhand
                    if qtytoorder > 0:
                        qtytoorder = self.adjust_qty(qtytoorder, item_plan_master_doc)
                    cm = self.create_action(
                        item_plan_master_doc=item_plan_master_doc,
                        mrp_date=getdate(),
                        mrp_qty=qtytoorder,
                        note="Safety Stock"
                    )
                    qty_ordered = cm["qty_ordered"]
                    onhand += qty_ordered
                counter += 1
            log_msg = _("MRP Calculation LLC {0} Finished - Nbr. products: {1}").format(llc - 1, counter)
            self.log("_mrp_calculation",log_msg)

        self.log("_mrp_calculation","End MRP calculation")

    def add_rescheduled_record(self, move, rescheduled_qty, rescheduled_date, ref_docname):
        reschedule_record = frappe._dict()
        for field in reschedule_item_fields:
            reschedule_record[field] = move.get(field)
        if move.partner_type == "Supplier":
            reschedule_record['supplier'] = move.partner
        key = (move.ref_docname, move.ref_doc_item, rescheduled_date)
        exist_reschedule = self.rescheduled_items_map.get(key)
        if exist_reschedule:
            exist_reschedule['rescheduled_qty'] += rescheduled_qty
            exist_reschedule['qty'] = move.qty
            exist_reschedule['details'].append([ref_docname,rescheduled_date,rescheduled_qty])
        else:
            reschedule_record['rescheduled_qty'] = rescheduled_qty
            reschedule_record['rescheduled_date'] = rescheduled_date
            reschedule_record['details'] = [[ref_docname,rescheduled_date,rescheduled_qty]]
            self.rescheduled_items_map[key] = reschedule_record

    def adjust_qty(self, qty, plan_master):
        """单个计划订单都按最小订单，最小包装处理会导致很多小订单需求转最小订单，总计划数变大，
            将该逻辑移至计划订单转工单、采购订单功能，合并后处理
        """
        if plan_master.stock_uom in self.whole_number_uom_list:
            qty = ceil(qty)     
        return qty

    def create_action(self, item_plan_master_doc, mrp_date, mrp_qty, ref_doctype = None, ref_docname = None,
            ref_doc_item = None, ref_doc_item_idx = None, note=None,values=None, warehouse=None, customer=None,bom_no=None):
        if not values:
            values = {}
        action_date, date_supply = self._get_action_and_supply_dates(item_plan_master_doc, mrp_date, mrp_qty)
        return self.create_plan_order(item_plan_master_doc, mrp_qty,
                 ref_doctype, ref_docname, ref_doc_item, ref_doc_item_idx, note,date_supply, action_date,
                  values=values, warehouse = warehouse,customer = customer, bom_no = bom_no)

    def _get_action_and_supply_dates(self, item_plan_master_doc, mrp_date, mrp_qty):
        mrp_date_supply = getdate() if mrp_date < getdate() else mrp_date
        if item_plan_master_doc.is_dummy_item:
            lead_time = 0
        elif item_plan_master_doc.daily_capacity:
            lead_time =  ceil(mrp_qty / item_plan_master_doc.daily_capacity)
        else:
            lead_time = item_plan_master_doc.lead_time_days or 0        
        mrp_action_date = mrp_date_supply if not lead_time else self.plan_days(mrp_date_supply, -1 * lead_time)
        return mrp_action_date, mrp_date_supply              

    def plan_days(self, input_date, days):
        schedule_date = self.plan_days_map.get((input_date, days))
        if not schedule_date:
            holidays = self.holidays
            if holidays:
                schedule_date = input_date
                i = 0
                while i < abs(days):
                    schedule_date = add_days(schedule_date, 1 if days>0 else -1)                
                    if not schedule_date in holidays: i += 1                          
            else:
                schedule_date = add_days(input_date, days)
            self.plan_days_map[(input_date, days)] = schedule_date                           
        return schedule_date
    
    def create_plan_order(self,item_plan_master_doc,mrp_qty,ref_doctype, ref_docname, ref_doc_item, ref_doc_item_idx=None,
        note =None, mrp_date_supply = None,mrp_action_date = None,values=None, warehouse=None, customer=None,bom_no=None):        
        if self._exclude_from_mrp(item_plan_master_doc.item_code, item_plan_master_doc.plant):
            values["qty_ordered"] = 0.0
            return values

        qty_ordered = values.get("qty_ordered", 0.0) if values else 0.0
        qty_to_order = mrp_qty
        
        supply_method = item_plan_master_doc.supply_method
        is_sub_contracted_item = item_plan_master_doc.is_sub_contracted_item
        if (supply_method in ['Manufacture','Subcontract'] or 
            supply_method == "Purchase" and is_sub_contracted_item):
            bom_no = (self.sub_assembly_bom_map.get(item_plan_master_doc.item_code) 
                     or bom_no or item_plan_master_doc.default_bom)
        while qty_ordered < mrp_qty:
            qty = self.adjust_qty(qty_to_order, item_plan_master_doc)
            order_data = self._prepare_plan_order_data(
                item_plan_master_doc, qty_to_order, qty, mrp_date_supply, mrp_action_date, 
                ref_doctype, ref_docname, ref_doc_item, ref_doc_item_idx, note, warehouse = warehouse,
                customer = customer, bom_no = bom_no
            )
            qty_to_order -= qty
            self.plan_orders.append(order_data)
            qty_ordered = qty_ordered + qty
            if (( supply_method in ['Manufacture','Subcontract'] or 
                  supply_method == "Purchase" and is_sub_contracted_item) and bom_no):
                self.explode_action(item_plan_master_doc, mrp_action_date, ref_docname, 
                    qty, plan_order = order_data.name, customer = customer, bom_no = bom_no)

        values["qty_ordered"] = qty_ordered

        if item_plan_master_doc.item_code in self.debug_items_set:
            log_msg = _("{0}: qty_ordered = {1}").format(
                item_plan_master_doc.item_code
                or item_plan_master_doc.item_code,
                qty_ordered,
            )            
            self.log("create_plan_order", log_msg)
        return values

    def _prepare_plan_order_data(self, item_plan_master_doc, qty_to_order, qty, 
            mrp_date_supply, mrp_action_date, ref_doctype, ref_docname, ref_doc_item,
            ref_doc_item_idx=None, note=None, warehouse=None, customer=None, bom_no=None):
        self.plan_order_id += 1
        supply_method = item_plan_master_doc.supply_method
        if supply_method == "Purchase" and item_plan_master_doc.is_sub_contracted_item:
            supply_method = "Subcontract"
        default_supplier = item_plan_master_doc.default_supplier
        warehouse = warehouse or item_plan_master_doc.default_warehouse
        is_subcontracted = 1 if ref_doctype == "Purchase Order" and supply_method == 'Subcontract' else 0
        alert = []
        #处理到期日或订单发布日早于计划执行日的异常情况，将订单发布日设为计划执行日，记录
        rescheduled_due_date, rescheduled_order_release_date = None, None
        if mrp_date_supply < getdate() or mrp_action_date < getdate():
            alert.append(_("Due date or order release date before planning date"))
            rescheduled_order_release_date = getdate()
            if supply_method == "Manufacture":
                rescheduled_due_date = self.plan_days(rescheduled_order_release_date, item_plan_master_doc.lead_time_days)
            else:
                rescheduled_due_date = add_days(rescheduled_order_release_date, item_plan_master_doc.lead_time_days)
        if not warehouse:
            alert.append(_("Missing default warehouse"))
        if not supply_method:
            alert.append(_("Missing Supply Method"))
        else:
            if supply_method in ("Purchase", "Subcontract") and not default_supplier:
                alert.append(_("Missing Vendor"))
            if  supply_method in ("Manufacture", "Subcontract") and not bom_no:
                alert.append(_("Missing BOM")) 
            if  supply_method == "Customer Provided" and not customer:
                alert.append(_("Customer provided item missing customer"))             
        return frappe._dict({
            #PL2100010000,PL前缀，21 年后两位   0001 MR运行序号 0001 计划订单序号
            "name": f"PL{self.year_last_2}{self.mrp_id}{str(self.plan_order_id).zfill(4)}",
            "item_code": item_plan_master_doc.item_code,
            "item_name": item_plan_master_doc.item_name,
            "item_group": item_plan_master_doc.item_group,
            "planner": item_plan_master_doc.planner,
            "warehouse": warehouse,
            "company":self.company,
            "qty": qty,
            "stock_uom":item_plan_master_doc.stock_uom,
            "due_date": mrp_date_supply,
            "order_release_date": mrp_action_date,
            "rescheduled_due_date": rescheduled_due_date,
            "rescheduled_order_release_date": rescheduled_order_release_date,
            "supply_method": supply_method,
            "convertible": not item_plan_master_doc.is_dummy_item,
            "supplier": default_supplier,
            "bom_no": bom_no,
            "qty_released": 0.0,
            "ref_doctype": ref_doctype,
            "is_subcontracted": is_subcontracted,
            "ref_docname": ref_docname,
            "ref_doc_item": ref_doc_item,
            "ref_doc_item_idx": ref_doc_item_idx,
            "required_qty": qty_to_order,
            "note": note or _("Planned supply for: {0}").format(ref_docname),
            "is_fixed": False,
            "alert": "\n".join(alert),
            "has_alert": 1 if alert else 0,
            "customer":customer
            #"doctype":"Plan Order"
        })

    def get_mrp_inventory_data(self):      
        doc_list = [frappe._dict(
                        dict(
                            origin = 'Plan Item',
                            item_code = p.item_code,
                            company = self.company,
                            mrp_date= p.due_date,
                            qty = p.original_qty)
                        ) 
                    for p in self.plan_items
                ]
        doc_list.extend([frappe._dict(
                            dict(
                                origin = 'Plan Order',
                                item_code = p.item_code,
                                company = p.company,
                                mrp_date= p.due_date,
                                qty = p.qty
                                )
                            ) 
                        for p in self.plan_orders + self.plan_order_items
                    ])
        doc_list = sorted(doc_list, key=lambda k: (k['item_code'], k['mrp_date']))
        return doc_list

    def _mrp_final_process(self):
        self.log("_mrp_final_process", "Start MRP final process")  
        doc_list = self.get_mrp_inventory_data()
        mrp_inventory_list = []
        last_item_code, last_mrp_date, last_running_availability = None, None, 0
        for doc in doc_list:
            item_code, mrp_date, qty = (doc.item_code, doc.mrp_date, doc.qty)            
            supply_qty, demand_qty, to_procure = (0,0,0)
            if doc.origin == 'Plan Order':
                to_procure = qty
            else:
                supply_qty = qty if (qty and qty > 0) else 0
                demand_qty = (qty * -1) if (qty and qty < 0) else 0
            if item_code != last_item_code or mrp_date != last_mrp_date:    
                row = frappe._dict({
                        'item_code' : doc.item_code,
                        'company': doc.company,
                        'mrp_date': doc.mrp_date,
                        'mrp': self.name,
                        'supply_qty': supply_qty,
                        'demand_qty': demand_qty,
                        'to_procure': to_procure
                    })
                mrp_inventory_list.append(row)    
                if item_code != last_item_code:  #新物料，取初始库存
                    row['initial_on_hand_qty'] = self.get_item_code_onhand(item_code)
                elif mrp_date != last_mrp_date:  #日期不同，初始数量带上一行
                    row['initial_on_hand_qty'] = running_availability    
            else:
                row['supply_qty'] += supply_qty
                row['demand_qty'] += demand_qty
                row['to_procure'] += to_procure
            running_availability = row['initial_on_hand_qty'] + row['supply_qty'] - row['demand_qty'] + row['to_procure']
            row['running_availability'] = running_availability   
            last_item_code, last_mrp_date = (item_code, mrp_date)                    
        self.log("_mrp_final_process","End MRP final process")
        self.save_to_database(mrp_inventory_list)
        #计划订单关联到库存 
        sql = """update `tabPlan Order` po inner join `tabMRP Inventory` mi on
                    po.due_date = mi.mrp_date and po.item_code= mi.item_code and 
                    po.company=mi.company and po.mrp = mi.mrp 
                    set po.mrp_inventory = mi.name"""
        frappe.db.sql(sql)

    def save_to_database(self, mrp_inventory_list = []):
        if mrp_inventory_list:                
            insert_multi('MRP Inventory', mrp_inventory_list)

        fields =  ["name","note","rescheduleable_qty"] + reschedule_item_fields
        rescheduled_items = [dict(r, **{
            "name": f"RESC{self.year_last_2}{self.mrp_id}{str(idx+1).zfill(5)}",
            "rescheduleable_qty": r.qty + r.rescheduled_qty,
            "note":"\n".join(";".join(str(f) for f in row) for row in r.details)
                            })
                    for idx, r in enumerate(self.rescheduled_items_map.values())]
        insert_multi('Rescheduled Item', rescheduled_items, fields, add_idx = True)

        fields =  ["parent", "parenttype", "parentfield",  "ref_doctype","ref_docname", "ref_doc_item", "ref_doc_item_idx",
            "item_code", "parent_item_code", "bom_no", "qty", "uom", "stock_uom", "conversion_factor",'transaction_date',
            "item_name",  "partner_type",  "partner", "due_date", "note","sort_key","warehouse","customer"
            ]
        mrp_plan_item_fields = ["order_open_qty","planned_qty","sales_order","sales_order_item", "is_subcontracted" ]    
        #conversion_factor是浮点数字段，数据库不接受空值，设个默认值1
        plan_items = [dict(r, **{'parent': self.name,
                                'parenttype':"Multi Level MRP",
                                'parentfield':"items",
                                'order_open_qty': r.get('order_open_qty') or 0,
                                'planned_qty': r.get('planned_qty') or 0,
                                'conversion_factor': r.get('conversion_factor') or 1,
                                'is_subcontracted': r.get('is_subcontracted') or 0
                                }
                        )
                    for r in self.plan_items]
        insert_multi('MRP Plan Item', plan_items, fields + mrp_plan_item_fields, add_idx = True) 
        #与MRP Plan Item表结构一致，parent,parenttype,parentfield已在产生记录时赋值了
        plan_order_items = [dict(r, **{'conversion_factor': r.get('conversion_factor') or 1})
                    for r in self.plan_order_items]               
        insert_multi('Plan Order Item', plan_order_items, fields, add_idx = True)

        fields =  ["name", "company","item_code", "qty","uom","stock_uom","item_name","due_date",
                 "order_release_date", 'item_group','planner',
                 "rescheduled_order_release_date","rescheduled_due_date", "alert", "warehouse","bom_no",
                 "mrp_action","supply_method","convertible", "supplier","customer", "note","mrp"]                          
                           
        plan_orders = [dict(r, **{'mrp': self.name}) for r in self.plan_orders]
        insert_multi('Plan Order', plan_orders) #, fields
        fields = ["parent","parenttype","parentfield","step","time","message"]
        insert_multi("MRP Run Log", self.logs, fields, add_idx = True)

@frappe.whitelist()
def run_mrp_multi_level(mrp_doc_name):
    """具体逻辑详见readme.md
    """
    self = frappe.get_doc('Multi Level MRP', mrp_doc_name)    
    self.initialize()
    self._mrp_cleanup()
    mrp_lowest_llc = self._low_level_code_calculation()
    if not self.items: #如果网页界面获取过待计划物料并修改过参数后，直接使用。
        self.get_plan_items()
    else:
        #如果作为doc method,js会将doc作为dict传回python,此时会导致items里的due_date字段变成文本类型，与其它日期比较时出现 
        #文本与日期比较的错误，使用单独的方法，重新从数据库读取doc内容。
        self.plan_items = [row.as_dict() for row in self.items]
    self._mrp_calculation(mrp_lowest_llc)
    self._mrp_final_process()
    #self.reload()  #此方法在单独对象方法调用时需要

@frappe.whitelist()
def run_mrp_multi_level_job(mrp_doc_name=None, company = None, via_background_job = False):
    if not mrp_doc_name:
        if not company:
            company = frappe.db.get_single_value('Global Defaults', 'default_company')
        if not company: return
        mrp = frappe.new_doc("Multi Level MRP")
        mrp.company = company
        mrp.save()
        mrp_doc_name = mrp.name
    if via_background_job:
        frappe.enqueue(
            method="zelin_am.agile_manufacturing.doctype.multi_level_mrp.multi_level_mrp.run_mrp_multi_level",
            mrp_doc_name=mrp_doc_name,
            timeout=40000,
            now=True,
        )
    else:
        run_mrp_multi_level(mrp_doc_name)


"""
from zelin_am.agile_manufacturing.doctype.multi_level_mrp.multi_level_mrp import *
self = frappe.get_doc({'doctype':'Multi Level MRP','company':'frappedemo','item_code':'计划成品'})
self = frappe.get_last_doc('Multi Level MRP')
frappe.flags.multi_level_mrp_debug = True
self.initialize()
self._mrp_cleanup()
mrp_lowest_llc = self._low_level_code_calculation()
self.get_plan_items(save_db = True)
self._mrp_calculation(mrp_lowest_llc)
doc_list = self.get_mrp_inventory_data()
self._mrp_final_process()
from zelin_am.agile_manufacturing.doctype.multi_level_mrp.multi_level_mrp import *
self = frappe.new_doc('Multi Level MRP')
self.save()
frappe.flags.multi_level_mrp_debug = True
run_mrp_multi_level(self.name)
"""    